/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.HivDbIntegration;

import db.HivDbIntegration.hivData.HivData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import units.CellType;
import units.Expression;
import units.ExpressionLibrary;
import units.Gene;
import units.GeneLibrary;
import units.MonkeyType;
import units.Sample;
import utils.Handle;

/**
 *
 * @author jamie
 */
public class IntersectionSummary {

    private Handle handle;
    private String sql;
    private int minPmIds = 2;
    private boolean regulationOnly = true;
    private GeneLibrary geneLibrary;

    public void fillIntersectionsTable() {
        handle = Handle.getConnectedHandle("monkeys");
        geneLibrary = new GeneLibrary();
        ExpressionLibrary expressionLibrary = new ExpressionLibrary();
        expressionLibrary.populateForAllSamples(geneLibrary);
        HivData hivData = new HivData();
        hivData.build(geneLibrary, minPmIds, regulationOnly);
        Set<Integer> expressionGenes = expressionLibrary.getAllProbeIds();
        HashSet<Integer> allProbeIds = new HashSet<Integer>();
        for (int probeId : expressionGenes) {
            allProbeIds.add(probeId);
        }

        for (int probeId : allProbeIds) {
            addData(probeId, hivData, expressionLibrary);
        }
        handle.closeConnection();

    }

    public void makeIntersectionTable() {
        handle = Handle.getConnectedHandle("monkeys");
        sql = "drop table if exists hivDbIntersection";
        handle.runSQLupdate(sql);
        sql = "create table hivDbIntersection (probeId int primary key not null, noOfSamples_AL int not null,noOfSamples_AP int not null,noOfSamples_ML int not null,noOfSamples_MP int not null, hivInteraction char(1) not null, isg char(1) not null)";
        handle.runSQLupdate(sql);
        handle.closeConnection();
    }

    private void addData(int probeId, HivData hivData, ExpressionLibrary expressionLibrary) {
        int count_AL = 0;
        int count_AP = 0;
        int count_ML = 0;
        int count_MP = 0;

        for (Sample s : expressionLibrary.getAllSamples()) {
            TreeMap<Integer, Expression> de = expressionLibrary.getExpression(probeId, s);
            if (de.size() > 0) {
                if (s.getCellType().equals(CellType.L)) {
                    if (s.getMonkeyType().equals(MonkeyType.A)) {
                        count_AL++;
                    } else {
                        count_ML++;
                    }
                } else {
                    if (s.getMonkeyType().equals(MonkeyType.A)) {
                        count_AP++;
                    } else {
                        count_MP++;
                    }
                }
            }
        }
        String hivGene = "n";
        List<Gene> genes = getGenes(probeId);
        for (Gene gene : genes) {
            if (hivData.getGenes().contains(gene)) {
                hivGene = "y";
            }
        }

        boolean isg = isISG(genes);

        String isgStr = "n";
        if (isg) {
            isgStr = "y";
        }
        sql = "insert into hivDbIntersection values (" + probeId + ", " + count_AL + ", " + count_AP + ", " + count_ML + ", " + count_MP + ", '" + hivGene + "', '" + isgStr + "')";
        handle.runSQLupdate(sql);
    }

    public List<Integer> getIntersectionGenes() {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_AP=6 and noOfSamples_ML=6 and noOfSamples_MP=6 and hivInteraction='y'";
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getIntersectionProbes(CellType cellType) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (cellType.equals(CellType.L)) {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_ML=6 and hivInteraction='y'";
        } else {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AP=6 and noOfSamples_MP=6 and hivInteraction='y'";
        }
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getISGs(CellType cellType) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (cellType.equals(CellType.L)) {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_ML=6 and isg='y'";
        } else {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AP=6 and noOfSamples_MP=6 and isg='y'";
        }
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getMultiArrayGenes(GeneLibrary library, MonkeyType mt) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (mt.equals(MonkeyType.A)) {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_AP=6";
        } else {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_ML=6 and noOfSamples_MP=6";
        }
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getMultiArrayProbes(GeneLibrary library) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();

        sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_AP=6 and noOfSamples_ML=6 and noOfSamples_MP=6";

        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getMultiArrayClusterProbes(MonkeyType monkeyType) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (monkeyType.equals(MonkeyType.A)) {
            sql = "select distinct t1.probeId from hivDbIntersection t1, clusterMembership t2, probeidlookup t3 where t1.probeId=t3.probeId and t2.probeId=t3.probeId and noOfSamples_AL=6 and noOfSamples_AP=6";
        } else {
            sql = "select distinct t1.probeId from hivDbIntersection t1, clusterMembership t2, probeidlookup t3 where t1.probeId=t3.probeId and t2.probeId=t3.probeId and noOfSamples_ML=6 and noOfSamples_MP=6";
        }

        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getMultiArrayClusterProbes(MonkeyType monkeyType, CellType ct) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();

        String sql = "select distinct t1.probeId from hivDbIntersection t1, clusterMembership t2 where t1.probeId=t2.probeId and noOfSamples_" + monkeyType.name() + ct.name() + "=6 order by probeId";
        System.out.println(sql);


        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getMultiArrayDifferentiallyExpressedGenes(MonkeyType monkeyType) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (monkeyType.equals(MonkeyType.A)) {
            sql = "select distinct t1.probeId from hivDbIntersection t1, differentialExpression t2 where noOfSamples_AL=6 and noOfSamples_AP=6 and t1.probeId=t2.probeId and t2.monkeyType='A' and t2.pValue<0.1 and timePoint>0";
        } else {
            sql = "select distinct t1.probeId from hivDbIntersection t1, differentialExpression t2 where noOfSamples_ML=6 and noOfSamples_MP=6 and t1.probeId=t2.probeId and t2.monkeyType='M' and t2.pValue<0.1 and timePoint>0";
        }
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getIntersectionGenes(MonkeyType mt) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        if (mt.equals(MonkeyType.A)) {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_AL=6 and noOfSamples_AP=6 and hivInteraction='y'";
        } else {
            sql = "select distinct probeId from hivDbIntersection where noOfSamples_ML=6 and noOfSamples_MP=6 and hivInteraction='y'";
        }
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    public List<Integer> getIntersectionGenes(MonkeyType mt, CellType ct) {
        handle = Handle.getConnectedHandle("monkeys");
        ArrayList<Integer> probes = new ArrayList<Integer>();
        String var = "noOfSamples_" + mt.name() + ct.name();
        sql = "select distinct probeId from hivDbIntersection where " + var + "=6 and hivInteraction='y'";
        ResultSet rs = handle.runSQLquery(sql);
        try {
            while (rs.next()) {
                probes.add(rs.getInt("probeId"));
            }
            rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return probes;
    }

    private boolean isISG(List<Gene> genes) {
        boolean isg = false;
        for (Gene g : genes) {
            sql = "select distinct gId from hcv.isgs where gId=" + g.getId();
            ResultSet rs = handle.runSQLquery(sql);
            ;
            try {
                if (rs.next()) {

                    isg = true;

                }
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return isg;
    }

    private List<Gene> getGenes(int probeId) {
        String sql = "select distinct gId from probeIdLookup where probeId=" + probeId;
        ResultSet rs = handle.runSQLquery(sql);
        List<Gene> genes = new ArrayList<Gene>();
        try {
            while (rs.next()) {
                int gId = rs.getInt("gId");
                if (gId != -1) {
                    Gene g = geneLibrary.getGene(gId);
                    genes.add(g);
                }
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(IntersectionSummary.class.getName()).log(Level.SEVERE, null, ex);
        }
        return genes;
    }
}
